﻿<?xml version="1.0" encoding="utf-8"?><Articles><Article><ArticlesID>871</ArticlesID><Title>Hướng dẫn load XML v&amp;#224;o DataSet v&amp;#224; Insert v&amp;#224;o SQL Server</Title><Body>&amp;lt;p&amp;gt;Đặt ra 1 b&amp;amp;agrave;i to&amp;amp;aacute;n : bạn được b&amp;amp;ecirc;n A giao cho dữ liệu của 1 bảng (User)  bằng XML v&amp;amp;agrave; y&amp;amp;ecirc;u cầu bạn Insert to&amp;amp;agrave;n bộ dữ liệu đ&amp;amp;oacute; v&amp;amp;agrave;o DB của m&amp;amp;igrave;nh. M&amp;amp;igrave;nh  sẽ giải quyết như sau:&amp;lt;/p&amp;gt;
&amp;lt;p&amp;gt;file.xml&amp;lt;/p&amp;gt;
&amp;lt;pre class=&amp;quot;brush: xhtml;&amp;quot; title=&amp;quot;code&amp;quot;&amp;gt;
&amp;amp;lt;newForumAsh&amp;amp;gt;
   &amp;amp;lt;user&amp;amp;gt;
        &amp;amp;lt;userid&amp;amp;gt;1&amp;amp;lt;/userid&amp;amp;gt;
        &amp;amp;lt;username&amp;amp;gt;admin&amp;amp;lt;/username&amp;amp;gt;
        &amp;amp;lt;password&amp;amp;gt;3349311e8bdb5b11d9f49ff2047e5631&amp;amp;lt;/password&amp;amp;gt;
        &amp;amp;lt;email&amp;amp;gt;admin@ash.com&amp;amp;lt;/email&amp;amp;gt;
   &amp;amp;lt;/user&amp;amp;gt;
   &amp;amp;lt;user&amp;amp;gt;
          &amp;amp;lt;userid&amp;amp;gt;2&amp;amp;lt;/userid&amp;amp;gt;
          &amp;amp;lt;username&amp;amp;gt;user12&amp;amp;lt;/username&amp;amp;gt;
          &amp;amp;lt;password&amp;amp;gt;e493716f73d8e1c410bfbfe3e910d3af&amp;amp;lt;/password&amp;amp;gt;
          &amp;amp;lt;email&amp;amp;gt;user12@ash.com&amp;amp;lt;/email&amp;amp;gt;
     &amp;amp;lt;/user&amp;amp;gt;
   &amp;amp;lt;user&amp;amp;gt;
          &amp;amp;lt;userid&amp;amp;gt;3&amp;amp;lt;/userid&amp;amp;gt;
          &amp;amp;lt;username&amp;amp;gt;user1&amp;amp;lt;/username&amp;amp;gt;
          &amp;amp;lt;password&amp;amp;gt;f65696f9d46fa5b8c70b2e20c045cdbf&amp;amp;lt;/password&amp;amp;gt;
          &amp;amp;lt;email&amp;amp;gt;user1@ash.com&amp;amp;lt;/email&amp;amp;gt;
     &amp;amp;lt;/user&amp;amp;gt;
&amp;amp;lt;/newForumAsh&amp;amp;gt; &amp;lt;/pre&amp;gt;
&amp;lt;p&amp;gt;&amp;amp;nbsp;&amp;lt;/p&amp;gt;
&amp;lt;p&amp;gt;trong trang aspx bạn tạo 1 form upload cho người d&amp;amp;ugrave;ng để upload file xml  l&amp;amp;ecirc;n &amp;lt;br /&amp;gt;
sau đ&amp;amp;oacute; trong code behind &amp;lt;br /&amp;gt;
aspx.vb&amp;lt;/p&amp;gt;
&amp;lt;pre class=&amp;quot;brush: vb;&amp;quot; title=&amp;quot;code&amp;quot;&amp;gt;
  Public ValueInsert As Integer = 0
  Private Sub btnImport_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnImport.Click
      Try
          Dim strEmail As String
          Dim strPassword As String
          Dim strUserName As String
          'luu ra folder tam tren may chu
          Dim strTempPath As String = Server.MapPath(&amp;amp;quot;~/Temp/&amp;amp;quot;)

          'upload len folder tam
          If fileUpload.HasFile Then
              'chi upload file xml
              If fileUpload.PostedFile.ContentType = &amp;amp;quot;text/xml&amp;amp;quot; Then
                  'luu xuong may chu
                  fileUpload.SaveAs(strTempPath &amp;amp;amp; fileUpload.FileName)
                  'khoi tao dataset
                  Dim ds As DataSet = New DataSet()
                  'load file sau khi upload vao stream
                  Dim fsReadXml As System.IO.FileStream = New IO.FileStream(strTempPath &amp;amp;amp; fileUpload.FileName, IO.FileMode.Open)
                  'dataset doc file xml tu stream
                  ds.ReadXml(fsReadXml)
                  'dong stream
                  fsReadXml.Close()
                  'khoi tao 1 datatable de dua dataset vao
                  Dim dbTable As DataTable = ds.Tables(0)
                  'duyet den tung dong trong table
                  For Each row As DataRow In dbTable.Rows
                      'gan cho moi bien vao cac truong
                      strEmail = row(&amp;amp;quot;email&amp;amp;quot;).ToString()
                      strPassword = row(&amp;amp;quot;password&amp;amp;quot;).ToString()
                      strUserName = row(&amp;amp;quot;username&amp;amp;quot;).ToString()

                      If strEmail = vbNullString Then
                          strEmail = &amp;amp;quot; &amp;amp;quot;
                      End If

                      If strPassword = vbNullString Then
                          strPassword = &amp;amp;quot; &amp;amp;quot;
                      End If

                      If strUserName = vbNullString Then
                          strUserName = &amp;amp;quot; &amp;amp;quot;
                      End If
                      
                      'tao user
                      CreateUser(strUserName, strPassword, strEmail)
                      'tang gia tri insert len 1 sau moi lan insert
                      ValueInsert = ValueInsert + 1

                  Next
                  'xoa file vua upload sau khi import xong
                  File.Delete(strTempPath &amp;amp;amp; fileUpload.FileName)
                  Response.Write(&amp;amp;quot;* Delete Temp File&amp;amp;lt;br /&amp;amp;gt;&amp;amp;quot;)
                  'ok
                  Response.Write(&amp;amp;quot;* Import Complete&amp;amp;lt;br /&amp;amp;gt;&amp;amp;quot;)
                  'ghi ra da insert bao nhieu ban ghi
                  Response.Write(&amp;amp;quot;* Insert &amp;amp;quot; &amp;amp;amp; ValueInsert.ToString() &amp;amp;amp; &amp;amp;quot; records.&amp;amp;lt;br /&amp;amp;gt;&amp;amp;quot;)
              Else
                  Response.Write(&amp;amp;quot;* Please choose XML File&amp;amp;quot;)
              End If
          Else
              Response.Write(&amp;amp;quot;* File Upload Require&amp;amp;quot;)
          End If
      Catch ex As Exception
          Response.Write(ex.Message)
      End Try
end sub


  Public Sub CreateUser(ByVal NickName As String, ByVal Password As String, ByVal Email As String, ByVal Salt As String)
      Try
          'khoi tao ket noi
          dim dataConn As System.Data.SqlClient.SqlConnection
          dim dataComm As System.Data.SqlClient.SqlCommand
          If dataConn Is Nothing Then dataConn = New System.Data.SqlClient.SqlConnection
          '* neu trang thai cua dataConn la Closed thi moi mo them ket noi
          If dataConn.State = ConnectionState.Closed Then
              dataConn.ConnectionString = vbNullString
              dataConn.ConnectionString = strDataString
              dataConn.Open()
          End If

          If dataComm Is Nothing Then dataComm = New System.Data.SqlClient.SqlCommand
          dataComm.Connection = dataConn
          dataComm.CommandTimeout = 20
          dataComm.CommandType = CommandType.Text

          'Query Insert
          Dim strQuery As String = &amp;amp;quot;INSERT INTO [tbl_User]([UserName],[Password],[Email]) VALUES('&amp;amp;quot; &amp;amp;amp; NickName &amp;amp;amp; &amp;amp;quot;','&amp;amp;quot; &amp;amp;amp; Password &amp;amp;amp; &amp;amp;quot;','&amp;amp;quot; &amp;amp;amp; Email &amp;amp;amp; &amp;amp;quot;')&amp;amp;quot;
          'Execute
          dataComm.CommandText = strQuery
          dataComm.ExecuteNonQuery()
          
          'dong connect sau khi insert
          If Not dataConn Is Nothing Then If dataConn.State = ConnectionState.Open Then dataConn.Close()
          dataConn = Nothing
      Catch ex As Exception
          Throw New ApplicationException(ex.Message)
      End Try
  End Sub
&amp;lt;/pre&amp;gt;
&amp;lt;p&amp;gt;&amp;amp;nbsp;&amp;lt;/p&amp;gt;</Body><CommentEnabled>True</CommentEnabled><ViewCount>0</ViewCount><ReleaseDate>4/15/2010 7:42:01 AM</ReleaseDate><TotalComment>0</TotalComment><IsMedia>False</IsMedia><IsPicture>False</IsPicture><IsVote>True</IsVote><ArticlePassword /><PostVisible>0</PostVisible><ArticlePath>/2010/3/27/Huong-dan-load-XML-DataSet-Insert-SQL-Server-367</ArticlePath><Approved>True</Approved><ImagePath /><CategoryId>26</CategoryId><ArrayCatID>26|25</ArrayCatID></Article></Articles>